using System;
using System.Data;
using System.Collections.Generic;
namespace eTMS.DAO
{
    /********************************BEGIN LopDAO****************************************/
    ///<summary>
    /// <para>Author: Auto generated class by CSharp Code Generator v2.0</para>
    /// <para>Date:10/08/2010 58:03:30</para>
    /// <para>Class Name: LopDAO</para>
    /// <para>Description: Class access to table Lop in database. Class for coder code here</para>
    ///</summary>


    public partial class LopDAO
    {
        public DataSet SelectAllDSLopWithSoSinhVienGiaoVienByKhoaHocNganhHocKhoa(int KhoaHoc_ID, int NganhHoc_ID, int Khoa_ID)
        {
            IDbCommand cmd = this.CreateCommand();
            string Filter = "";
            if (KhoaHoc_ID > 0)
            {
                Filter += " AND Lop.KhoaHoc_ID=" + KhoaHoc_ID.ToString();
            }
            if (NganhHoc_ID > 0)
            {
                Filter += " AND Lop.NganhHoc_ID=" + NganhHoc_ID.ToString();
            }
            if (Khoa_ID > 0)
            {
                Filter += " AND Khoa.Khoa_ID=" + Khoa_ID.ToString();
            }
            if (Filter.Length > 0) Filter = Filter.Substring(4);
            cmd.CommandText = @"SELECT        Lop.Lop_ID, Lop.MaLop, Lop.TenLop, Lop.SoSinhVienToiDa, Lop.MoTaLop, Lop.GiaoVienPhuTrach_ID, Lop.NganhHoc_ID, Lop.KhoaHoc_ID, 
                                                COUNT(LopSinhVien.SinhVien_ID) AS SoSinhVien, NganhHoc.MaNganhHoc, NganhHoc.TenNganhHoc, Khoa.MaKhoa, Khoa.TenKhoa, KhoaHoc.TenKhoaHoc,GiaoVien.HoTenGiaoVien
                                FROM            Lop LEFT JOIN
                                                     LopSinhVien ON Lop.Lop_ID = LopSinhVien.Lop_ID INNER JOIN
                                                     NganhHoc ON Lop.NganhHoc_ID = NganhHoc.NganhHoc_ID INNER JOIN
                                                     Khoa ON NganhHoc.Khoa_ID = Khoa.Khoa_ID LEFT JOIN
                                                     KhoaHoc ON KhoaHoc.KhoaHoc_ID = Lop.KhoaHoc_ID LEFT JOIN
                                                     GiaoVien ON Lop.GiaoVienPhuTrach_ID=GiaoVien.GiaoVien_ID
                                " + (Filter.Length > 0 ? " WHERE " + Filter : "") + @"
                                GROUP BY Lop.Lop_ID, Lop.MaLop, Lop.TenLop, Lop.SoSinhVienToiDa, Lop.MoTaLop, Lop.GiaoVienPhuTrach_ID, Lop.NganhHoc_ID, Lop.KhoaHoc_ID, NganhHoc.MaNganhHoc, 
                                                     NganhHoc.TenNganhHoc, Khoa.MaKhoa, Khoa.TenKhoa, KhoaHoc.TenKhoaHoc,GiaoVien.HoTenGiaoVien";
            return this.SelectDS(cmd);
        }
    

    }
    /*****************************************************************************************************/
    /**********************************END LopDAO*****************************************/
    /*****************************************************************************************************/
}

